import Database from 'better-sqlite3';
import dbManager from './DatabaseManager';
import { BaseEntity, TableInfo } from './BaseEntity';

export interface QueryOptions {
  where?: { [key: string]: any };
  orderBy?: string;
  limit?: number;
  offset?: number;
}

export class BaseService<T extends object> {
  protected db: Database.Database;
  protected entityClass: new () => T;
  protected tableInfo: TableInfo;

  constructor(entityClass: any) {
    this.db = dbManager.getDatabase();
    this.entityClass = entityClass;
    this.tableInfo = entityClass.getTableInfo();
    
    // 自动创建表
    const createTableSQL = entityClass.getCreateTableSQL();
    this.db.exec(createTableSQL);
  }

  /**
   * 插入记录
   */
  insert(entity: Partial<T>): number {
    const keys = Object.keys(entity);
    const values = Object.values(entity);
    const placeholders = keys.map(() => '?').join(', ');
    
    const sql = `INSERT INTO ${this.tableInfo.name} (${keys.join(', ')}) VALUES (${placeholders})`;
    
    const stmt = this.db.prepare(sql);
    const result = stmt.run(...values);
    
    return result.lastInsertRowid as number;
  }

  /**
   * 批量插入记录
   */
  batchInsert(entities: Partial<T>[]): void {
    if (entities.length === 0) return;
    
    const db = this.db;
    const transaction = db.transaction((items: Partial<T>[]) => {
      for (const item of items) {
        this.insert(item);
      }
    });
    
    transaction(entities);
  }

  /**
   * 通过ID查询记录
   */
  getById(id: number | string): T | null {
    const primaryKey = this.tableInfo.primaryKey || 'id';
    const sql = `SELECT * FROM ${this.tableInfo.name} WHERE ${primaryKey} = ?`;
    
    const stmt = this.db.prepare(sql);
    const result = stmt.get(id);
    
    return result as T || null;
  }

  /**
   * 查询符合条件的所有记录
   */
  find(options: QueryOptions = {}): T[] {
    let sql = `SELECT * FROM ${this.tableInfo.name}`;
    const params: any[] = [];
    
    // 处理WHERE条件
    if (options.where && Object.keys(options.where).length > 0) {
      const whereConditions = Object.entries(options.where).map(([key, value]) => {
        params.push(value);
        return `${key} = ?`;
      });
      
      sql += ` WHERE ${whereConditions.join(' AND ')}`;
    }
    
    // 排序
    if (options.orderBy) {
      sql += ` ORDER BY ${options.orderBy}`;
    }
    
    // 分页
    if (options.limit !== undefined) {
      sql += ` LIMIT ?`;
      params.push(options.limit);
      
      if (options.offset !== undefined) {
        sql += ` OFFSET ?`;
        params.push(options.offset);
      }
    }
    
    const stmt = this.db.prepare(sql);
    const results = stmt.all(...params);
    
    return results as T[];
  }

  /**
   * 查询符合条件的单条记录
   */
  findOne(options: QueryOptions = {}): T | null {
    options.limit = 1;
    const results = this.find(options);
    return results.length > 0 ? results[0] : null;
  }

  /**
   * 统计符合条件的记录数
   */
  count(options: QueryOptions = {}): number {
    let sql = `SELECT COUNT(*) as count FROM ${this.tableInfo.name}`;
    const params: any[] = [];
    
    // 处理WHERE条件
    if (options.where && Object.keys(options.where).length > 0) {
      const whereConditions = Object.entries(options.where).map(([key, value]) => {
        params.push(value);
        return `${key} = ?`;
      });
      
      sql += ` WHERE ${whereConditions.join(' AND ')}`;
    }
    
    const stmt = this.db.prepare(sql);
    const result = stmt.get(...params) as { count: number } | undefined;
    
    return result?.count || 0;
  }

  /**
   * 更新记录
   */
  update(entity: Partial<T>, whereCondition?: { [key: string]: any }): number {
    const primaryKey = this.tableInfo.primaryKey || 'id';
    const updateData = { ...entity };
    
    // 移除主键，避免更新主键
    if (primaryKey in updateData && !whereCondition) {
      const id = updateData[primaryKey as keyof typeof updateData];
      delete updateData[primaryKey as keyof typeof updateData];
      whereCondition = { [primaryKey]: id };
    }
    
    if (!whereCondition || Object.keys(whereCondition).length === 0) {
      throw new Error('更新操作必须提供条件');
    }
    
    const updateKeys = Object.keys(updateData);
    if (updateKeys.length === 0) {
      return 0;
    }
    
    const updateValues = Object.values(updateData);
    const wherePairs = Object.entries(whereCondition);
    const whereValues = wherePairs.map(p => p[1]);
    
    const sql = `
      UPDATE ${this.tableInfo.name} 
      SET ${updateKeys.map(key => `${key} = ?`).join(', ')} 
      WHERE ${wherePairs.map(([key]) => `${key} = ?`).join(' AND ')}
    `;
    
    const stmt = this.db.prepare(sql);
    const result = stmt.run(...updateValues, ...whereValues);
    
    return result.changes;
  }

  /**
   * 删除记录
   */
  delete(whereCondition: { [key: string]: any }): number {
    const wherePairs = Object.entries(whereCondition);
    
    if (wherePairs.length === 0) {
      throw new Error('删除操作必须提供条件');
    }
    
    const whereValues = wherePairs.map(p => p[1]);
    
    const sql = `
      DELETE FROM ${this.tableInfo.name} 
      WHERE ${wherePairs.map(([key]) => `${key} = ?`).join(' AND ')}
    `;
    
    const stmt = this.db.prepare(sql);
    const result = stmt.run(...whereValues);
    
    return result.changes;
  }

  /**
   * 通过ID删除记录
   */
  deleteById(id: number | string): number {
    const primaryKey = this.tableInfo.primaryKey || 'id';
    return this.delete({ [primaryKey]: id });
  }

  /**
   * 执行自定义SQL查询
   */
  query(sql: string, params: any[] = []): any[] {
    const stmt = this.db.prepare(sql);
    return stmt.all(...params);
  }

  /**
   * 执行自定义SQL更新
   */
  execute(sql: string, params: any[] = []): Database.RunResult {
    const stmt = this.db.prepare(sql);
    return stmt.run(...params);
  }
} 